Mikmak MsSQL Data Definition Language
Home

Mikmak MsSQL Data Definition Language

Mikmak MsSQL Data Definition Language

We maken scripts om de database en de tabellen voor de Mikmak webshop aan te maken.

Probleem

We maken de database en de tabellen niet aan met behulp van een visuele designer maar met behulp van SQL scripts.

Design

Vooraleer de database of een tabel te maken gaan we na als die al bestaat. Indien dit het geval is, deleten we eerst de tabel.

Oplossing

Database maken

-- An Orm Apart -- Sunday 3rd of January 2016 12:47:49 PM
-- 
-- If database does not exist, create the database
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'Mikmak')
BEGIN
	DROP DATABASE Mikmak
END
CREATE DATABASE Mikmak
GO

Meer info vind je in Een database maken en in Default locaties database bestanden.

Country

Over de tabel Country valt niet veel te zeggen. Let erop hoe je we een Unique constraint toevoegen. Vooraleer de tabel te creëren gaan we na of de tabel al bestaat. Als de tabel bestaat wordt die eerst verwijderd.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Country
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel Country te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Country')
BEGIN
    DROP TABLE [Country]
END
GO
CREATE TABLE "Country" (
    "Code" NVARCHAR (2) NOT NULL,
    "Latitude" FLOAT NULL,
    "Longitude" FLOAT NULL,
    "Name" NVARCHAR (255) NOT NULL,
    "ShippingCostMultiplier" FLOAT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Country_Id PRIMARY KEY(Id),
    CONSTRAINT uc_Country_Code UNIQUE (Code),
    CONSTRAINT uc_Country_Name UNIQUE (Name));

OrderStatus

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE OrderStatus
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel OrderStatus te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='OrderStatus')
BEGIN
    DROP TABLE [OrderStatus]
END
GO
CREATE TABLE "OrderStatus" (
    "Name" NVARCHAR (255) NOT NULL,
    "Description" NVARCHAR (1024) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_OrderStatus_Id PRIMARY KEY(Id),
    CONSTRAINT uc_OrderStatus_Name UNIQUE (Name));

UnitBase

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE UnitBase
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel UnitBase te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='UnitBase')
BEGIN
    DROP TABLE [UnitBase]
END
GO
CREATE TABLE "UnitBase" (
    "Name" NVARCHAR (255) NOT NULL,
    "Description" NVARCHAR (1024) NULL,
    "ShippingCostMultiplier" FLOAT NULL,
    "Code" NVARCHAR (2) NOT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_UnitBase_Id PRIMARY KEY(Id),
    CONSTRAINT uc_UnitBase_Name UNIQUE (Name),
    CONSTRAINT uc_UnitBase_Code UNIQUE (Code));

ShippingMethod

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE ShippingMethod
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel ShippingMethod te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='ShippingMethod')
BEGIN
    DROP TABLE [ShippingMethod]
END
GO
CREATE TABLE "ShippingMethod" (
    "Name" NVARCHAR (255) NOT NULL,
    "Description" NVARCHAR (1024) NULL,
    "Price" DECIMAL(6,2) NOT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_ShippingMethod_Id PRIMARY KEY(Id),
    CONSTRAINT uc_ShippingMethod_Name UNIQUE (Name));

Supplier

USE "Mikmak";
-- Vooraleer de tabel Supplier te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Supplier')
BEGIN
    DROP TABLE [Supplier]
END
GO
CREATE TABLE "Supplier" (
    "Code" NVARCHAR (10) NOT NULL,
    "Name" NVARCHAR (255) NOT NULL,
    "Contact" NVARCHAR (255) NULL,
    "Address" NVARCHAR (255) NULL,
    "City" NVARCHAR (255) NULL,
    "Region" NVARCHAR (80) NULL,
    "PostalCode" VARCHAR (20) NULL,
    "IdCountry" INT NULL,
    "Phone" VARCHAR (40) NULL,
    "Mobile" VARCHAR (40) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Supplier_Id PRIMARY KEY(Id),
    CONSTRAINT uc_Supplier_Code UNIQUE (Code),
    CONSTRAINT uc_Supplier_Name UNIQUE (Name),
    CONSTRAINT fk_SupplierIdCountry FOREIGN KEY ("IdCountry") REFERENCES "Country" ("Id"));

Curstomer

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Customer
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel Customer te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Customer')
BEGIN
    DROP TABLE [Customer]
END
GO
CREATE TABLE "Customer" (
    "NickName" NVARCHAR (10) NOT NULL,
    "FirstName" NVARCHAR (255) NOT NULL,
    "LastName" NVARCHAR (255) NOT NULL,
    "Address1" NVARCHAR (255) NOT NULL,
    "Address2" NVARCHAR (255) NULL,
    "City" NVARCHAR (255) NOT NULL,
    "Region" NVARCHAR (80) NULL,
    "PostalCode" VARCHAR (20) NOT NULL,
    "IdCountry" INT NOT NULL,
    "Phone" VARCHAR (40) NULL,
    "Mobile" VARCHAR (40) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Customer_Id PRIMARY KEY(Id),
    CONSTRAINT fk_CustomerCountry FOREIGN KEY ("IdCountry") REFERENCES "Country" ("Id"));

Product

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Product
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel Product te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Product')
BEGIN
    DROP TABLE [Product]
END
GO
CREATE TABLE "Product" (
    "Description" NVARCHAR (1024) NULL,
    "Name" NVARCHAR (255) NOT NULL,
    "Price" FLOAT NULL,
    "ShippingCost" FLOAT NULL,
    "TotalRating" INT NULL,
    "Thumbnail" VARCHAR (255) NULL,
    "Image" VARCHAR (255) NULL,
    "DiscountPercentage" FLOAT NULL,
    "Votes" INT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Product_Id PRIMARY KEY(Id),
    "IdSupplier" INT NOT NULL,
    CONSTRAINT uc_Product_Name UNIQUE (Name),
    CONSTRAINT fk_ProductIdSupplier FOREIGN KEY ("IdSupplier") REFERENCES "Supplier" ("Id"));

Order

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Order
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel Order te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Order')
BEGIN
    DROP TABLE [Order]
END
GO
CREATE TABLE "Order" (
    "OrderDate" DATETIME NOT NULL,
    "ShippingDate" DATETIME NOT NULL,
    "Comment" NVARCHAR (512) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Order_Id PRIMARY KEY(Id),
    "IdCustomer" INT NOT NULL,
    "IdShippingMethod" INT NOT NULL,
    "IdStatus" INT NOT NULL,
    CONSTRAINT fk_OrderIdCustomer FOREIGN KEY ("IdCustomer") REFERENCES "Customer" ("Id"),
    CONSTRAINT fk_OrderIdShippingMethod FOREIGN KEY ("IdShippingMethod") REFERENCES "ShippingMethod" ("Id"),
    CONSTRAINT fk_OrderIdStatus FOREIGN KEY ("IdStatus") REFERENCES "OrderStatus" ("Id"));

OrderItem

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE OrderItem
-- Created on Sunday 3rd of January 2016 01:23:07 PM
-- 
USE "Mikmak";
-- Vooraleer de tabel OrderItem te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='OrderItem')
BEGIN
    DROP TABLE [OrderItem]
END
GO
CREATE TABLE "OrderItem" (
    "IdProduct" INT NOT NULL,
    "IdOrder" INT NOT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_OrderItem_Id PRIMARY KEY(Id),
    "Quantity" DECIMAL(4,2) NULL,
    CONSTRAINT fk_OrderItemIdProduct FOREIGN KEY ("IdProduct") REFERENCES "Product" ("Id"),
    CONSTRAINT fk_OrderItemIdOrder FOREIGN KEY ("IdOrder") REFERENCES "Order" ("Id"));

Als je de code hierboven uitvoert moet je rekening met de afhankelijkheden die bepaald worden door foreign keys. De volgorde waarin je de tabellen verwijdert is anders dan de volgorde waarin je de tabellen aanmaakt. Daarom gaan we eerst de tabellen deleten in de juiste volgorde:

USE "_13875_JefInghelbrecht";

-- Vooraleer de tabel Customer te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Customer')
BEGIN
    DROP TABLE [Customer]
END
GO

-- Vooraleer de tabel Product te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Product')
BEGIN
    DROP TABLE [Product]
END
GO

-- Vooraleer de tabel Supplier te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Supplier')
BEGIN
    DROP TABLE [Supplier]
END
GO

-- Vooraleer de tabel OrderItem te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='OrderItem')
BEGIN
    DROP TABLE [OrderItem]
END
GO
-- Vooraleer de tabel Order te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Order')
BEGIN
    DROP TABLE [Order]
END
GO

-- Vooraleer de tabel Country te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='Country')
BEGIN
    DROP TABLE [Country]
END
GO
-- Vooraleer de tabel UnitBase te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='UnitBase')
BEGIN
    DROP TABLE [UnitBase]
END
GO

-- Vooraleer de tabel ShippingMethod te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='ShippingMethod')
BEGIN
    DROP TABLE [ShippingMethod]
END
GO

-- Vooraleer de tabel OrderStatus te creëren, test als
-- de tabel al bestaat. Als de tabel al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT * FROM sys.tables WHERE name='OrderStatus')
BEGIN
    DROP TABLE [OrderStatus]
END
GO

En dan de tabellen creëren in de juiste volgorde, namelijk volgens de foreign keys afhankelijkheden:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Country
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";

CREATE TABLE "Country" (
    "Code" NVARCHAR (2) NOT NULL,
    "Latitude" FLOAT NULL,
    "Longitude" FLOAT NULL,
    "Name" NVARCHAR (255) NOT NULL,
    "ShippingCostMultiplier" FLOAT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Country_Id PRIMARY KEY(Id),
    CONSTRAINT uc_Country_Code UNIQUE (Code),
    CONSTRAINT uc_Country_Name UNIQUE (Name));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE OrderStatus
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "OrderStatus" (
    "Name" NVARCHAR (255) NOT NULL,
    "Description" NVARCHAR (1024) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_OrderStatus_Id PRIMARY KEY(Id),
    CONSTRAINT uc_OrderStatus_Name UNIQUE (Name));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE UnitBase
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "UnitBase" (
    "Name" NVARCHAR (255) NOT NULL,
    "Description" NVARCHAR (1024) NULL,
    "ShippingCostMultiplier" FLOAT NULL,
    "Code" NVARCHAR (2) NOT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_UnitBase_Id PRIMARY KEY(Id),
    CONSTRAINT uc_UnitBase_Name UNIQUE (Name),
    CONSTRAINT uc_UnitBase_Code UNIQUE (Code));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE ShippingMethod
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "ShippingMethod" (
    "Name" NVARCHAR (255) NOT NULL,
    "Description" NVARCHAR (1024) NULL,
    "Price" DECIMAL(6,2) NOT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_ShippingMethod_Id PRIMARY KEY(Id),
    CONSTRAINT uc_ShippingMethod_Name UNIQUE (Name));

USE "_13875_JefInghelbrecht";
CREATE TABLE "Supplier" (
    "Code" NVARCHAR (10) NOT NULL,
    "Name" NVARCHAR (255) NOT NULL,
    "Contact" NVARCHAR (255) NULL,
    "Address" NVARCHAR (255) NULL,
    "City" NVARCHAR (255) NULL,
    "Region" NVARCHAR (80) NULL,
    "PostalCode" VARCHAR (20) NULL,
    "IdCountry" INT NULL,
    "Phone" VARCHAR (40) NULL,
    "Mobile" VARCHAR (40) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Supplier_Id PRIMARY KEY(Id),
    CONSTRAINT uc_Supplier_Code UNIQUE (Code),
    CONSTRAINT uc_Supplier_Name UNIQUE (Name),
    CONSTRAINT fk_SupplierIdCountry FOREIGN KEY ("IdCountry") REFERENCES "Country" ("Id"));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Customer
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "Customer" (
    "NickName" NVARCHAR (10) NOT NULL,
    "FirstName" NVARCHAR (255) NOT NULL,
    "LastName" NVARCHAR (255) NOT NULL,
    "Address1" NVARCHAR (255) NOT NULL,
    "Address2" NVARCHAR (255) NULL,
    "City" NVARCHAR (255) NOT NULL,
    "Region" NVARCHAR (80) NULL,
    "PostalCode" VARCHAR (20) NOT NULL,
    "IdCountry" INT NOT NULL,
    "Phone" VARCHAR (40) NULL,
    "Mobile" VARCHAR (40) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Customer_Id PRIMARY KEY(Id),
    CONSTRAINT fk_CustomerCountry FOREIGN KEY ("IdCountry") REFERENCES "Country" ("Id"));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Product
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "Product" (
    "Description" NVARCHAR (1024) NULL,
    "Name" NVARCHAR (255) NOT NULL,
    "Price" FLOAT NULL,
    "ShippingCost" FLOAT NULL,
    "TotalRating" INT NULL,
    "Thumbnail" VARCHAR (255) NULL,
    "Image" VARCHAR (255) NULL,
    "DiscountPercentage" FLOAT NULL,
    "Votes" INT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Product_Id PRIMARY KEY(Id),
    "IdSupplier" INT NOT NULL,
    CONSTRAINT uc_Product_Name UNIQUE (Name),
    CONSTRAINT fk_ProductIdSupplier FOREIGN KEY ("IdSupplier") REFERENCES "Supplier" ("Id"));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Order
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "Order" (
    "OrderDate" DATETIME NOT NULL,
    "ShippingDate" DATETIME NOT NULL,
    "Comment" NVARCHAR (512) NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_Order_Id PRIMARY KEY(Id),
    "IdCustomer" INT NOT NULL,
    "IdShippingMethod" INT NOT NULL,
    "IdStatus" INT NOT NULL,
    CONSTRAINT fk_OrderIdCustomer FOREIGN KEY ("IdCustomer") REFERENCES "Customer" ("Id"),
    CONSTRAINT fk_OrderIdShippingMethod FOREIGN KEY ("IdShippingMethod") REFERENCES "ShippingMethod" ("Id"),
    CONSTRAINT fk_OrderIdStatus FOREIGN KEY ("IdStatus") REFERENCES "OrderStatus" ("Id"));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE OrderItem
-- Created on Sunday 3rd of January 2016 01:23:07 PM
--
USE "_13875_JefInghelbrecht";
CREATE TABLE "OrderItem" (
    "IdProduct" INT NOT NULL,
    "IdOrder" INT NOT NULL,
    "Id" INT NOT NULL IDENTITY(1, 1),
    CONSTRAINT pk_OrderItem_Id PRIMARY KEY(Id),
    "Quantity" DECIMAL(4,2) NULL,
    CONSTRAINT fk_OrderItemIdProduct FOREIGN KEY ("IdProduct") REFERENCES "Product" ("Id"),
    CONSTRAINT fk_OrderItemIdOrder FOREIGN KEY ("IdOrder") REFERENCES "Order" ("Id"));

JI
2017-01-18 12:30:10